set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_getSearchResults' AND type = 'P') DROP PROCEDURE mov_getSearchResults
GO


-- =============================================
-- Modified:    Sourov Chatterjee
-- Create date: 6/3/2011
-- Description:	This procedure gets Search results

-- =============================================
CREATE PROCEDURE [dbo].[mov_getSearchResults] 
@searchtext varchar(50) ="Bello"
AS
BEGIN

    SELECT @searchtext = '%' + RTRIM(@searchtext) + '%'
	select m.movie_id , m.movie_name, (md.movie_dir_fname + ' ' + md.movie_dir_lname)as dir , mg.movie_genre as genre,
    m.movie_image, m.short_desc 
    from movie m JOIN movie_director md 
    on m.movie_dir_id = md.movie_dir_id
    JOIN movie_genre mg 
    ON m.movie_genre_id = mg.movie_genre_id
    where m.movie_name like  @searchtext or	     
          md.movie_dir_fname like @searchtext or
          md.movie_dir_lname like @searchtext or
          mg.movie_genre like @searchtext   
	

END














